1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmAdvanceEntryRecord1
4
5 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
6 Me.Close()
7 End Sub
8 Sub GetData()
9 Try
10 con = New SqlConnection(cs)
11 con.Open()
12 Dim sql As String = "SELECT RTRIM(Staff.StaffID),RTRIM(Staff.StaffName),RTRIM(Designation),sum(Amount)-sum(Deduction) FROM Staff Inner join AdvanceEntry on Staff.St_ID=AdvanceEntry.StaffID group by StaffName,Staff.StaffID,Designation having (sum(Amount)-sum(Deduction)) > 0 order by StaffName"
13 cmd = New SqlCommand(sql, con)
14 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
15 dgw.Rows.Clear()
16 While (rdr.Read() = True)
17 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3))
18 End While
19 con.Close()
20 Catch ex As Exception
21 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
22 End Try
23 End Sub
24 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
25 GetData()
26 End Sub
27 Sub Reset()
28 txtStaffName.Text = ""
29 GetData()
30 End Sub
31 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
32 Reset()
33 End Sub
34
35
36 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
37 Me.Close()
38 End Sub
39
40 Private Sub btnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
41 Dim rowsTotal, colsTotal As Short
42 Dim I, j, iC As Short
43 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
44 Dim xlApp As New Excel.Application
45 Try
46 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
47 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
48 xlApp.Visible = True
49
50 rowsTotal = dgw.RowCount
51 colsTotal = dgw.Columns.Count - 1
52 With excelWorksheet
53 .Cells.Select()
54 .Cells.Delete()
55 For iC = 0 To colsTotal
56 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
57 Next
58 For I = 0 To rowsTotal - 1
59 For j = 0 To colsTotal
60 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
61 Next j
62 Next I
63 .Rows("1:1").Font.FontStyle = "Bold"
64 .Rows("1:1").Font.Size = 12
65
66 .Cells.Columns.AutoFit()
67 .Cells.Select()
68 .Cells.EntireColumn.AutoFit()
69 .Cells(1, 1).Select()
70 End With
71 Catch ex As Exception
72 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
73 Finally
74 'RELEASE ALLOACTED RESOURCES
75 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
76 xlApp = Nothing
77 End Try
78 End Sub
79
80 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
81 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
82 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
83 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
84 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
85 End If
86 Dim b As Brush = SystemBrushes.ControlText
87 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
88
89 End Sub
90
91 Private Sub txtStaffName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStaffName.TextChanged
92 Try
93 con = New SqlConnection(cs)
94 con.Open()
95 Dim sql As String = "SELECT RTRIM(Staff.StaffID),RTRIM(Staff.StaffName),RTRIM(Designation),sum(Amount)-sum(Deduction) FROM Staff Inner join AdvanceEntry on Staff.St_ID=AdvanceEntry.StaffID and StaffName like '" & txtStaffName.Text & "%' group by StaffName,Staff.StaffID,Designation having (sum(Amount)-sum(Deduction)) > 0 order by StaffName"
96 cmd = New SqlCommand(sql, con)
97 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
98 dgw.Rows.Clear()
99 While (rdr.Read() = True)
100 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3))
101 End While
102 con.Close()
103 Catch ex As Exception
104 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
105 End Try
106 End Sub
107
108 End Class
